package com.jqb.jspcart.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

public class SQLHandler {
	
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	private ResultSetMetaData rsmd = null;
	public SQLHandler(){
		conn = DBHandler.newInstance().getConnection();
	}
	
	public static SQLHandler newInstance(){
		return new SQLHandler();
	}
	//查询符合条件的记录,并把每条记录封装成对象数组
	public ArrayList<Object[]> executeQuery(String sql,String[] params) throws SQLException{
		ArrayList<Object[]> result = new ArrayList<Object[]>();
		pstmt = conn.prepareStatement(sql);
		for(int i=0;i<params.length;i++){
			pstmt.setObject(i+1, params[i]);
		}
		rs = pstmt.executeQuery();
		rsmd = rs.getMetaData();
		int columCount = rsmd.getColumnCount();
		Object[] obj = null;
		while(rs.next()){
			obj = new Object[columCount];
			for(int i=0;i<columCount;i++){
				obj[i] = rs.getObject(i+1);
			}
			result.add(obj);
		}
		//哪里使用资源哪里关闭
		DBHandler.close(conn,pstmt,rs);
		return result;
	}
	//执行增删改操作
	public boolean executeUpdate(String sql,String[] params) throws SQLException{
		boolean flag = false;
		String sql1 = "select id from users where name='"+params[0]+"'";
		pstmt = conn.prepareStatement(sql1);
		rs = pstmt.executeQuery();
		if(rs.next()){
			return flag;
		}else{
			pstmt = conn.prepareStatement(sql);
			int len = params.length;
			for(int i=0;i<len;i++){
				pstmt.setObject(i+1, params[i]);
			}
			int count = pstmt.executeUpdate();
			if(count>0) flag = true;
			DBHandler.close(conn, pstmt, rs);
			return flag;
		}
	}
	//返回符合条件的记录数
	public int executeQuery(String sql) throws SQLException {
		int count = 0;
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			count = rs.getInt(1);
		}
		DBHandler.close(conn,pstmt,rs);
		return count;
		
	}
}
